package com.kandinfo.isbnmanage.repository.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.kandinfo.base.common.converter.ConverterService;
import com.kandinfo.base.common.enums.EDataSource;
import com.kandinfo.base.common.util.DBUtil;
import com.kandinfo.base.common.util.JdbcBeanUtil;
import com.kandinfo.isbnmanage.domian.Homework;
import com.kandinfo.isbnmanage.domian.HomeworkPage;
import com.kandinfo.isbnmanage.dto.HomeworkPageDto;
import com.kandinfo.isbnmanage.dto.IsbnBusinessReqDto;
import com.kandinfo.isbnmanage.dto.IsbnInformationDto;
import com.kandinfo.isbnmanage.repository.IsbnBusinessRepository;
import com.kandinfo.isbnmanage.repository.IsbnInformationRepository;
import com.kandinfo.isbnmanage.util.AliyunOSSUtil;

@Repository
public class IsbnBusinessRepositoryImpl implements IsbnBusinessRepository{
	
	private final static Logger LOGGER = LoggerFactory.getLogger(IsbnBusinessRepositoryImpl.class);
	
	 @Autowired
	 private EntityManager entityManager;
	 
	 @Autowired
	 IsbnInformationRepository isbnInformationRepository;
	 
	 @Autowired
	 AliyunOSSUtil aliyunOSSUtil;

	@SuppressWarnings("unchecked")
	@Override
	public Integer getRepeatInfoCount(IsbnInformationDto isbnInformationResDto) {
		if (isbnInformationResDto == null) {
			return 1;
		}
		
		if (!(StringUtils.isNotEmpty(isbnInformationResDto.getName())
				&& isbnInformationResDto.getType() != null
				&& isbnInformationResDto.getGrade() != null
				&& isbnInformationResDto.getSubject() != null
				&& isbnInformationResDto.getEdition() != null 
				)) {
			return 0;
		}
		String sql = "select h.id from isbn_information h where h.name = '"+isbnInformationResDto.getName()+"' and h.type = "+isbnInformationResDto.getType()+
                " and h.grade = "+isbnInformationResDto.getGrade()+(" and h.subject = "+isbnInformationResDto.getSubject())+" and h.edition = "+isbnInformationResDto.getEdition() +
                (StringUtils.isEmpty(isbnInformationResDto.getPress())?" ":(" and h.press = '"+ isbnInformationResDto.getPress() +"' "))+
                (StringUtils.isEmpty(isbnInformationResDto.getTime())?" ":(" and h.time = '"+isbnInformationResDto.getTime()+"' "))+
                (StringUtils.isEmpty(isbnInformationResDto.getRevision())?" ":("and h.revision = '"+isbnInformationResDto.getRevision()+"' "));
		
		if (isbnInformationResDto.getId() != null) {
			sql += " and  h.id != " + isbnInformationResDto.getId() + " ";
		}
		Query nativeQuery = entityManager.createNativeQuery(sql);
		List<Long> result = nativeQuery.getResultList();
			
		return result.size();
	}
	
	@Override
	public void sysIsbn(IsbnBusinessReqDto isbnBusinessReqDto) {
		//判断重复作业本
		try {
			Long repeatHomeworkId = getRepeatHomeworkId(isbnBusinessReqDto.getHomework());
			if (repeatHomeworkId != null) {
				isbnBusinessReqDto.getIsbnInformation().setHomeworkId(repeatHomeworkId);
				if (isbnBusinessReqDto.getIsbnInformation().getHomeworkId().longValue() != repeatHomeworkId) {
					isbnInformationRepository.save(isbnBusinessReqDto.getIsbnInformation());
				}
				isbnBusinessReqDto.getHomework().setId(repeatHomeworkId);
			}
		} catch (Exception e) {
			isbnBusinessReqDto.setErrMsg("getRepeatHomeworkId ERROR!");
			return;
		}
		
		//处理数据
		if (isbnBusinessReqDto.getHomework().getId() != null) {
			//更新数据
			updateIsbn(isbnBusinessReqDto);
		}else {
			//新建数据
			saveIsbn(isbnBusinessReqDto);
		}
		if (StringUtils.isNotEmpty(isbnBusinessReqDto.getErrMsg())) {
			return;
		}
	}

	/**
	 * 新增
	 * @param isbnBusinessReqDto
	 */
	private void saveIsbn(IsbnBusinessReqDto isbnBusinessReqDto) {
		DBUtil db = new DBUtil(EDataSource.Homework_2.getProvinceId());
		Long homeworkId = null;
        try {
            db.getConnection().setAutoCommit(false);
            
            /*********************Homework************************/
    		String insertHomeworkSql = JdbcBeanUtil.getSqlOfInsertOne(isbnBusinessReqDto.getHomework());
    		homeworkId = Long.valueOf(db.exeUpdate(insertHomeworkSql, null));
    		
    		 /*********************IsbnInformation************************/
    		isbnBusinessReqDto.getIsbnInformation().setHomeworkId(homeworkId);
    		isbnInformationRepository.save(isbnBusinessReqDto.getIsbnInformation());
    		
            /*********************HomeworkPage************************/
            //新增
    		List<HomeworkPage> homeworkPageList = isbnBusinessReqDto.getHomeworkPageList();
    		String insertHomeworkPageSql = getInsertHomeworkPageSql(homeworkPageList, homeworkId);
    		if (StringUtils.isNotEmpty(insertHomeworkPageSql)) {
    			db.exeUpdate(insertHomeworkPageSql, null);
			}
    		String pageImageUrl = homeworkPageList.get(0).getPageImageUrl();
    		String sourceKey = pageImageUrl.substring(pageImageUrl.indexOf("isbn"), pageImageUrl.lastIndexOf("/"));
    		String destinationKey = "homework/"+homeworkId;
    		aliyunOSSUtil.copyAllObject(sourceKey, destinationKey);
    		//更新数据库中对应的信息
    		String upadteSql = "update homework_page hp set hp.page_image_url = replace(hp.page_image_url,'"+sourceKey+"','"
    		                    +destinationKey+"') where hp.homework_id = "+homeworkId+" and hp.status = '1'";
    		db.exeUpdate(upadteSql, null);
    		String upadtehomeworkSql = "update homework hp set hp.cover_image_url = replace(hp.cover_image_url,'"+sourceKey+"','"
                    +destinationKey+"') where hp.id = "+homeworkId;
            db.exeUpdate(upadtehomeworkSql, null);
    		/*********************HomeworkPageQuestion************************/
    		LOGGER.debug(" TODO add HomeworkPageQuestion sql ");
    		/*********************HomeworkPageSubquestion************************/
    		LOGGER.debug(" TODO add HomeworkPageSubquestion sql ");
    		
    		//提交事务
    		db.getConnection().commit();
        } catch (SQLException e) {
        	LOGGER.error("saveIsbn ERROR!", e);
        	isbnBusinessReqDto.setErrMsg("saveIsbn ERROR!");
			return;
        } finally {
            db.releaseConnection();
        }
	}

	/**
	 * 更新
	 * 
	 * @param isbnBusinessReqDto
	 */
	private void updateIsbn(IsbnBusinessReqDto isbnBusinessReqDto) {
		DBUtil db = new DBUtil(EDataSource.Homework_2.getProvinceId());
		Long homeworkId = isbnBusinessReqDto.getHomework().getId();
		try {
			db.getConnection().setAutoCommit(false);
			
			/*********************Homework************************/
			String updateHomeworkSql = JdbcBeanUtil.getUpdateSql(isbnBusinessReqDto.getHomework());
			db.exeUpdate(updateHomeworkSql, null);
			
			/*********************HomeworkPage************************/
			//假删
			String deleteHomeworkPageSql = "update homework_page hp set hp.status = '0' where hp.homework_id = "+homeworkId+" ";
			db.exeUpdate(deleteHomeworkPageSql, null);
			//新增
    		List<HomeworkPage> homeworkPageList = isbnBusinessReqDto.getHomeworkPageList();
    		String insertHomeworkPageSql = getInsertHomeworkPageSql(homeworkPageList, homeworkId);
    		if (StringUtils.isNotEmpty(insertHomeworkPageSql)) {
    			db.exeUpdate(insertHomeworkPageSql, null);
			}
    		String pageImageUrl = homeworkPageList.get(0).getPageImageUrl();
    		String sourceKey = pageImageUrl.substring(pageImageUrl.indexOf("isbn"), pageImageUrl.lastIndexOf("/"));
    		String destinationKey = "homework/"+homeworkId;
    		aliyunOSSUtil.copyAllObject(sourceKey, destinationKey);
    		//更新数据库中对应的信息
    		String upadteSql = "update homework_page hp set hp.page_image_url = replace(hp.page_image_url,'"+sourceKey+"','"
    		                    +destinationKey+"') where hp.homework_id = "+homeworkId+" and hp.status = '1'";
    		db.exeUpdate(upadteSql, null);
    		String upadtehomeworkSql = "update homework hp set hp.cover_image_url = replace(hp.cover_image_url,'"+sourceKey+"','"
                    +destinationKey+"') where hp.id = "+homeworkId;
            db.exeUpdate(upadtehomeworkSql, null);
			/*********************HomeworkPageQuestion************************/
    		LOGGER.debug(" TODO add HomeworkPageQuestion update sql ");
    		/*********************HomeworkPageSubquestion************************/
    		LOGGER.debug(" TODO add HomeworkPageSubquestion update sql ");
			
			//提交事务
    		db.getConnection().commit();
		} catch (SQLException e) {
        	LOGGER.error("updateIsbn ERROR!", e);
        	isbnBusinessReqDto.setErrMsg("updateIsbn ERROR!");
			return;
        } finally {
            db.releaseConnection();
        }
	}
	
	private String getInsertHomeworkPageSql(List<HomeworkPage> homeworkPageList,Long homeworkId){
		if (!CollectionUtils.isEmpty(homeworkPageList)) {
			List<HomeworkPageDto> copyList = new ArrayList<HomeworkPageDto>();
			for (HomeworkPage homeworkPage : homeworkPageList) {
				HomeworkPageDto copy = new HomeworkPageDto();
				copy = ConverterService.convert(homeworkPage, HomeworkPageDto.class);
				copy.setHomeworkId(homeworkId);
				copy.setId(null);
				copyList.add(copy);
			}
			String insertHomeworkPageSql = JdbcBeanUtil.getSqlOfInsertList(copyList,"homework_page");
			return insertHomeworkPageSql;
		}
		return null;
	}
	

	/**
     * 新建作业本时
     * 查询是否存在改作业本
     * 
     * 
     * @param isbnInformationResDto
     * @return				count
	 * @throws Exception 
     */
    private Long getRepeatHomeworkId(Homework homework) throws Exception{
    	DBUtil db = new DBUtil(EDataSource.Homework_2.getProvinceId());
        
        String sql = "select h.id as homeworkId from homework h where h.name = '"+homework.getName()+"' and h.type = "+homework.getType()+
                " and h.grade = "+homework.getGrade()+(" and h.subject = "+homework.getSubject())+" and h.edition = "+homework.getEdition() +
                (StringUtils.isEmpty(homework.getPress())?" ":(" and h.press = '"+ homework.getPress() +"' "))+
                (StringUtils.isEmpty(homework.getTime())?" ":(" and h.time = '"+homework.getTime()+"' "))+
                (StringUtils.isEmpty(homework.getRevision())?" ":("and h.revision = '"+homework.getRevision()+"' "));

        try{
            ResultSet rs = db.exeQuery(sql,null);
            while(rs.next()){
                return rs.getLong("homeworkId");
            }
        }catch(SQLException e){
            throw new Exception("getRepeatHomeworkId sql error",e);
        }finally {
            db.releaseConnection();
        }
        return null;
    }

	@Override
	public void upPulish(IsbnBusinessReqDto isbnBusinessReqDto) {
		DBUtil db = new DBUtil(EDataSource.Homework_2.getProvinceId());
		List<String> sqlList = new ArrayList<String>();
		Long homeworkId = isbnBusinessReqDto.getHomework().getId();
        try {
            db.getConnection().setAutoCommit(false);
            
            /*********************Homework************************/
    		String updateHomeworkSql = "update homework h set h.status = 0 where h.id = " + homeworkId + " ";
    		sqlList.add(updateHomeworkSql);
            /*********************HomeworkPage************************/
    		String updateHomeworkPageSql = " update homework_page hp set hp.status = 0 where hp.homework_id =  "  + homeworkId + " ";
    		sqlList.add(updateHomeworkPageSql);
    		/*********************HomeworkPageQuestion************************/
    		
    		/*********************HomeworkPageSubquestion************************/
    		
    		//执行
    		for (String sql : sqlList) {
    			db.exeUpdate(sql, null);
            }
    		db.getConnection().commit();
        } catch (SQLException e) {
        	LOGGER.error("upPulish ERROR!", e);
        	isbnBusinessReqDto.setErrMsg("saveIsbn ERROR!");
			return;
        } finally {
            db.releaseConnection();
        }
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer getRepeatInfoNameCount(String name, Long id) {
		String sql = "select h.id from isbn_information h where h.name = '"+name+"'";
		if (id != null) {
			sql += " and  h.id != " + id + " ";
		}
		Query nativeQuery = entityManager.createNativeQuery(sql);
		List<Long> result = nativeQuery.getResultList();
		return result.size();
	}
	 
}
